﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace ISHotel.Database
{
    [Serializable]
    public class TaxaTable : DbTable
    {
        public static String TABLE_NAME = "Taxa";

        public String SQL_SELECT = "SELECT * FROM Taxa ORDER BY id_taxy ASC";
        public String SQL_SELECT_id_taxy = "SELECT * FROM Taxa WHERE id_taxy=:id_taxy";
        public String SQL_INSERT = "INSERT INTO Taxa (nazev_taxy, popis_taxy, cena_taxy) VALUES (:nazev_taxy, :popis_taxy, :cena_taxy)";
        public String SQL_UPDATE = "UPDATE Taxa SET nazev_taxy=:nazev_taxy, popis_taxy=:popis_taxy, cena_taxy=:cena_taxy WHERE id_taxy=:id_taxy";
        public String SQL_DELETE = "DELETE FROM Taxa WHERE id_taxy=:id_taxy";

        public TaxaTable()
            : base(TABLE_NAME)
        {
        }

        /**
 * Insert the record.
 **/
        public int Insert(Taxa Taxa)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_INSERT);
            PrepareCommand(command, Taxa);
            int ret = mDatabase.Insert(command);
            mDatabase.Close();
            return ret;
        }

        /**
         * Update the record.
         **/
        public int Update(Taxa Taxa)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_UPDATE);

            command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_nazev_taxy, OracleDbType.Varchar2, Taxa.nazev_taxy.Length));
            command.Parameters[":" + Taxa.ATTR_nazev_taxy].Value = Taxa.nazev_taxy;

            if (Taxa.popis_taxy != null)
            {
                command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_popis_taxy, OracleDbType.Varchar2, Taxa.popis_taxy.Length));
                command.Parameters[":" + Taxa.ATTR_popis_taxy].Value = Taxa.popis_taxy;
            }
            else
            {
                command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_popis_taxy, OracleDbType.Varchar2, 15));
                command.Parameters[":" + Taxa.ATTR_popis_taxy].Value = "NOT PROVIDED";
            }

            command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_cena_taxy, OracleDbType.Decimal));
            command.Parameters[":" + Taxa.ATTR_cena_taxy].Value = Taxa.cena_taxy;
            
            command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_id_taxy, OracleDbType.Decimal));
            command.Parameters[":" + Taxa.ATTR_id_taxy].Value = Taxa.id_taxy;

            int ret = mDatabase.Update(command);
            mDatabase.Close();
            return ret;
        }

        /**
         * Prepare a command.
         **/

        private void PrepareCommand(OracleCommand command, Taxa Taxa)
        {

            command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_nazev_taxy, OracleDbType.Varchar2, Taxa.nazev_taxy.Length));
            command.Parameters[":" + Taxa.ATTR_nazev_taxy].Value = Taxa.nazev_taxy;

            if (Taxa.popis_taxy != null)
            {
                command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_popis_taxy, OracleDbType.Varchar2, Taxa.popis_taxy.Length));
                command.Parameters[":" + Taxa.ATTR_popis_taxy].Value = Taxa.popis_taxy;
            }
            else
            {
                command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_popis_taxy, OracleDbType.Varchar2, 15));
                command.Parameters[":" + Taxa.ATTR_popis_taxy].Value = "NOT PROVIDED";
            }


            command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_cena_taxy, OracleDbType.Decimal));
            command.Parameters[":" + Taxa.ATTR_cena_taxy].Value = Taxa.cena_taxy;

        }

        /**
         * Select records.
         **/
        public Collection<Taxa> Select()
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT);
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Taxa> Taxes = Read(reader);
            reader.Close();
            mDatabase.Close();
            return Taxes;
        }

        /**
         * Select the record.
         **/
        public Taxa Select(int id_taxy)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT_id_taxy);

            command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_id_taxy, OracleDbType.Decimal));
            command.Parameters[":" + Taxa.ATTR_id_taxy].Value = id_taxy;
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Taxa> Taxes = Read(reader);
            Taxa Taxa1 = null;
            if (Taxes.Count == 1)
            {
                Taxa1 = Taxes[0];
            }
            reader.Close();
            mDatabase.Close();
            return Taxa1;
        }

        private Collection<Taxa> Read(OracleDataReader reader)
        {
            Collection<Taxa> Taxes = new Collection<Taxa>();

            while (reader.Read())
            {
                Taxa Taxa = new Taxa();
                Taxa.id_taxy = (int)reader.GetDecimal(0);
                Taxa.nazev_taxy = reader.GetString(1);
                if (!reader.IsDBNull(2))
                    if (reader.GetString(2) != "NOT PROVIDED")
                        Taxa.popis_taxy = reader.GetString(2);
                Taxa.cena_taxy = (int)reader.GetDecimal(3);
                Taxes.Add(Taxa);
            }
            return Taxes;
        }

        /**
         * Delete the record.
         */
        public int Delete(int id_taxy)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_DELETE);

            command.Parameters.Add(new OracleParameter(":" + Taxa.ATTR_id_taxy, OracleDbType.Decimal));
            command.Parameters[":" + Taxa.ATTR_id_taxy].Value = id_taxy;
            int ret;
            try
            {
                ret = mDatabase.Delete(command);
            }
            catch (Exception e)
            {
                throw e;
            }
            mDatabase.Close();
            return ret;
        }
    }
}